from odoo import api, fields, models, _
import io
import base64
import unicodedata
from odoo.exceptions import UserError
from collections import defaultdict
try:
    import xlsxwriter
except Exception:  # pragma: no cover
    xlsxwriter = None


class DocumentsBackOrderWizard(models.TransientModel):
    _name = "documents.back_order.wizard"
    _description = "欠单对比向导"

    language_mode = fields.Selection(
        selection=[("zh", "中文"), ("en", "英文"), ("it", "意大利文")],
        string="语言",
        default="zh",
        required=True,
    )
    export_format = fields.Selection(
        selection=[("xlsx", "XLSX")],
        string="导出格式",
        default="xlsx",
        required=True,
    )
    date = fields.Date(string="日期", default=fields.Date.context_today, required=True)
    company_id = fields.Many2one(
        comodel_name="res.company",
        string="公司",
        default=lambda self: self.env.company,
        required=True,
    )
    purchase_id = fields.Many2one("purchase.order", string="采购订单", required=True)
    package_ids = fields.Many2many(
        comodel_name="stock.quant.package",
        relation="documents_back_order_wizard_package_rel",
        column1="wizard_id",
        column2="package_id",
        string="包装",
        required=True,
    )
    note = fields.Text(string="备注")

    # 生成的下载文件
    file_name = fields.Char(string="文件名", readonly=True)
    file_data = fields.Binary(string="文件", readonly=True)

    @api.onchange("purchase_id")
    def _onchange_purchase_id(self):
        if self.purchase_id:
            # 从采购订单的date_order预填日期（用户时区）
            if self.purchase_id.date_order:
                try:
                    export_date = fields.Datetime.context_timestamp(self, self.purchase_id.date_order).date()
                except Exception:
                    export_date = fields.Date.to_date(self.purchase_id.date_order)
                self.date = export_date

    # ------------------------- 辅助方法 -------------------------
    def _i18n_labels(self, lang):
        labels = {
            "zh": {
                "title": "尾单比较",
                "date": "日期",
                "number": "编号",
                "seq": "序号",
                "product": "产品",
                "desc": "描述",
                "purchase_qty": "采购数量",
                "actual_qty": "实际到货",
                "diff": "差异",
                "status": "状态",
                "company": "公司",
                "supplier": "供应商",
                "buyer": "采购员",
                "purchase_order": "采购订单",
                "packages": "包裹",
                "both_exist": "采购单和实际到货都存在的产品",
                "only_purchase": "采购单有但实际到货没有的产品（没发的货）",
                "only_actual": "采购单没有但实际到货存在的产品（发错的货）",
                "excess": "多了",
                "shortage": "少了",
                "missing": "缺货",
                "wrong_delivery": "错发",
            },
            "en": {
                "title": "Back Order Compare",
                "date": "Date",
                "number": "Number",
                "seq": "Seq",
                "product": "Product",
                "desc": "Description",
                "purchase_qty": "Purchase Qty",
                "actual_qty": "Actual Qty",
                "diff": "Difference",
                "status": "Status",
                "company": "Company",
                "supplier": "Supplier",
                "buyer": "Buyer",
                "purchase_order": "Purchase Order",
                "packages": "Packages",
                "both_exist": "Products in both Purchase and Actual",
                "only_purchase": "Products only in Purchase (Not Delivered)",
                "only_actual": "Products only in Actual (Wrong Delivery)",
                "excess": "Excess",
                "shortage": "Shortage",
                "missing": "Missing",
                "wrong_delivery": "Wrong Delivery",
            },
            "it": {
                "title": "Confronto Ordine Arretrato",
                "date": "Data",
                "number": "Numero",
                "seq": "N°",
                "product": "Prodotto",
                "desc": "Descrizione",
                "purchase_qty": "Qtà Acquisto",
                "actual_qty": "Qtà Effettiva",
                "diff": "Differenza",
                "status": "Stato",
                "company": "Azienda",
                "supplier": "Fornitore",
                "buyer": "Acquirente",
                "purchase_order": "Ordine d'Acquisto",
                "packages": "Pacchi",
                "both_exist": "Prodotti in entrambi Acquisto ed Effettivo",
                "only_purchase": "Prodotti solo in Acquisto (Non Consegnati)",
                "only_actual": "Prodotti solo in Effettivo (Consegna Errata)",
                "excess": "Eccesso",
                "shortage": "Carenza",
                "missing": "Mancante",
                "wrong_delivery": "Consegna Errata",
            },
        }
        return labels.get(lang or "zh")

    def _display_width(self, text):
        s = str(text or "")
        width = 0
        for ch in s:
            eaw = unicodedata.east_asian_width(ch)
            width += 2 if eaw in ("F", "W") else 1
        return width

    def _map_to_odoo_lang(self, lang_code):
        mapping = {
            "zh": "zh_CN",
            "en": "en_US",
            "it": "it_IT",
        }
        return mapping.get(lang_code, "zh_CN")

    def _get_valid_odoo_lang(self, lang_code):
        """Return lang_code only if installed in this DB; else return None."""
        if not lang_code:
            return None
        Lang = self.env["res.lang"]
        rec = Lang.search([("code", "=", lang_code)], limit=1)
        return rec.code if rec else None

    def _merge_purchase_lines(self, purchase_lines):
        """合并相同编号和描述的采购行"""
        merged_data = defaultdict(lambda: {"qty": 0.0, "lines": []})
        
        for line in purchase_lines:
            # 获取产品编号 - 优先条形码，然后new_variable，最后default_code
            product_code = line.product_id.product_tmpl_id.barcode or getattr(line.product_id, 'new_variable', None) or line.product_id.default_code or ""
            # 获取产品名称作为描述
            product_name = line.product_id.name or ""
            
            # 使用编号和名称作为合并的key
            key = (product_code, product_name)
            merged_data[key]["qty"] += line.product_qty
            merged_data[key]["lines"].append(line)
        
        # 转换为字典格式
        result = {}
        for (product_code, product_name), data in merged_data.items():
            result[product_code] = {
                "product_code": product_code,
                "product_name": product_name,
                "total_qty": data["qty"],
                "sample_line": data["lines"][0],  # 用于获取其他信息
            }
        
        return result

    def _merge_package_quants(self, packages):
        """合并包裹中相同编号和描述的产品"""
        merged_data = defaultdict(float)
        product_info = {}
        
        for package in packages:
            for quant in package.quant_ids:
                if quant.quantity <= 0:  # 跳过数量为0或负数的记录
                    continue
                    
                # 获取产品编号 - 优先条形码，然后new_variable，最后default_code
                product_code = quant.product_id.product_tmpl_id.barcode or getattr(quant.product_id, 'new_variable', None) or quant.product_id.default_code or ""
                # 获取产品名称作为描述
                product_name = quant.product_id.name or ""
                
                merged_data[product_code] += quant.quantity
                if product_code not in product_info:
                    product_info[product_code] = {
                        "product_code": product_code,
                        "product_name": product_name,
                        "product_id": quant.product_id,
                    }
        
        # 转换为字典格式
        result = {}
        for product_code, qty in merged_data.items():
            if product_code in product_info:
                result[product_code] = {
                    "product_code": product_code,
                    "product_name": product_info[product_code]["product_name"],
                    "total_qty": qty,
                    "product_id": product_info[product_code]["product_id"],
                }
        
        return result

    def _compare_data(self, purchase_data, package_data):
        """比较采购单和包裹数据，返回三个区域的数据"""
        both_exist = []  # 采购单和实际到货都存在
        only_purchase = []  # 只在采购单中存在
        only_actual = []  # 只在实际到货中存在
        
        all_product_codes = set(purchase_data.keys()) | set(package_data.keys())
        
        for product_code in all_product_codes:
            purchase_item = purchase_data.get(product_code)
            package_item = package_data.get(product_code)
            
            if purchase_item and package_item:
                # 两边都存在，比较数量
                diff = package_item["total_qty"] - purchase_item["total_qty"]
                both_exist.append({
                    "product_code": product_code,
                    "product_name": purchase_item["product_name"],
                    "purchase_qty": purchase_item["total_qty"],
                    "actual_qty": package_item["total_qty"],
                    "diff": diff,
                    "status": "excess" if diff > 0 else "shortage" if diff < 0 else "equal",
                    "sample_line": purchase_item["sample_line"],
                })
            elif purchase_item and not package_item:
                # 只在采购单中存在
                only_purchase.append({
                    "product_code": product_code,
                    "product_name": purchase_item["product_name"],
                    "purchase_qty": purchase_item["total_qty"],
                    "actual_qty": 0.0,
                    "diff": -purchase_item["total_qty"],
                    "status": "missing",
                    "sample_line": purchase_item["sample_line"],
                })
            elif not purchase_item and package_item:
                # 只在实际到货中存在
                only_actual.append({
                    "product_code": product_code,
                    "product_name": package_item["product_name"],
                    "purchase_qty": 0.0,
                    "actual_qty": package_item["total_qty"],
                    "diff": package_item["total_qty"],
                    "status": "wrong_delivery",
                })
        
        return both_exist, only_purchase, only_actual

    # ------------------------- 导出 -------------------------
    def action_export(self):
        self.ensure_one()
        if self.export_format != "xlsx":
            return {"type": "ir.actions.act_window_close"}
        if not xlsxwriter:
            raise UserError(_("The Python package 'xlsxwriter' is required to export XLSX files."))
        
        order = self.purchase_id
        packages = self.package_ids
        if not order:
            raise UserError(_("Please select a purchase order."))
        if not packages:
            raise UserError(_("Please select packages."))

        lang = self.language_mode or "zh"
        L = self._i18n_labels(lang)
        odoo_lang = self._get_valid_odoo_lang(self._map_to_odoo_lang(lang))

        # 获取采购单数据
        purchase_lines = order.order_line.filtered(lambda l: not l.display_type)
        purchase_data = self._merge_purchase_lines(purchase_lines)

        # 获取包裹数据
        package_data = self._merge_package_quants(packages)

        # 比较数据
        both_exist, only_purchase, only_actual = self._compare_data(purchase_data, package_data)

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {"in_memory": True})
        sheet = workbook.add_worksheet(L["title"])
        
        # 打印设置：A4 纵向，按页宽缩放，水平居中，边距
        try:
            sheet.set_portrait()
            sheet.set_paper(9)  # A4
            sheet.fit_to_pages(1, 0)
            sheet.center_horizontally()
            sheet.set_margins(left=0.3, right=0.3, top=0.6, bottom=0.8)
        except Exception:
            pass

        # 样式
        fmt_title = workbook.add_format({"bold": True, "font_size": 20, "align": "center", "valign": "vcenter"})
        fmt_subtitle = workbook.add_format({"bold": True, "font_size": 14, "align": "center", "valign": "vcenter"})
        fmt_header = workbook.add_format({"bold": True, "align": "center", "valign": "vcenter", "border": 1})
        fmt_cell = workbook.add_format({"align": "left", "valign": "vcenter", "border": 1})
        fmt_cell_center = workbook.add_format({"align": "center", "valign": "vcenter", "border": 1})
        fmt_cell_number = workbook.add_format({"align": "center", "valign": "vcenter", "border": 1, "num_format": "#,##0.00"})
        fmt_info = workbook.add_format({"align": "left", "valign": "vcenter"})

        # 设置列宽
        sheet.set_column("A:A", 8)   # 序号
        sheet.set_column("B:B", 20)  # 编号
        sheet.set_column("C:C", 40)  # 描述
        sheet.set_column("D:D", 12)  # 采购数量
        sheet.set_column("E:E", 12)  # 实际数量
        sheet.set_column("F:F", 12)  # 差异
        sheet.set_column("G:G", 15)  # 状态

        row = 0
        
        # 标题
        sheet.merge_range(row, 0, row, 6, L["title"], fmt_title)
        row += 2
        
        # 基本信息
        company_name = (self.company_id.display_name or self.company_id.name or "")
        supplier_name = (order.partner_id.display_name or order.partner_id.name or "")
        buyer = order.user_id and order.user_id.name or ""
        package_names = ", ".join(packages.mapped('name'))
        
        sheet.write(row, 0, f"{L['company']}: {company_name}", fmt_info)
        sheet.write(row, 3, f"{L['supplier']}: {supplier_name}", fmt_info)
        row += 1
        sheet.write(row, 0, f"{L['purchase_order']}: {order.name}", fmt_info)
        sheet.write(row, 3, f"{L['buyer']}: {buyer}", fmt_info)
        row += 1
        sheet.write(row, 0, f"{L['date']}: {fields.Date.to_string(self.date)}", fmt_info)
        row += 1
        sheet.write(row, 0, f"{L['packages']}: {package_names}", fmt_info)
        row += 2

        # 表头
        headers = [L["seq"], L["number"], L["desc"], L["purchase_qty"], L["actual_qty"], L["diff"], L["status"]]

        def write_section(title, data, start_row):
            current_row = start_row
            if not data:
                return current_row
                
            # 区域标题
            sheet.merge_range(current_row, 0, current_row, 6, title, fmt_subtitle)
            current_row += 1
            
            # 表头
            for col, header in enumerate(headers):
                sheet.write(current_row, col, header, fmt_header)
            current_row += 1
            
            # 数据
            for idx, item in enumerate(data, 1):
                status_text = ""
                if item["status"] == "excess":
                    status_text = f"{L['excess']} {abs(item['diff']):.2f}"
                elif item["status"] == "shortage":
                    status_text = f"{L['shortage']} {abs(item['diff']):.2f}"
                elif item["status"] == "missing":
                    status_text = L["missing"]
                elif item["status"] == "wrong_delivery":
                    status_text = L["wrong_delivery"]
                else:
                    status_text = ""
                
                # 根据语言设置描述
                description = item["product_name"]
                if odoo_lang and "sample_line" in item:
                    try:
                        description = item["sample_line"].product_id.with_context(lang=odoo_lang).name or item["product_name"]
                    except:
                        pass
                
                sheet.write(current_row, 0, idx, fmt_cell_center)
                sheet.write(current_row, 1, item["product_code"], fmt_cell)
                sheet.write(current_row, 2, description, fmt_cell)
                sheet.write_number(current_row, 3, item["purchase_qty"], fmt_cell_number)
                sheet.write_number(current_row, 4, item["actual_qty"], fmt_cell_number)
                sheet.write_number(current_row, 5, item["diff"], fmt_cell_number)
                sheet.write(current_row, 6, status_text, fmt_cell_center)
                current_row += 1
            
            return current_row + 1

        # 写入三个区域
        row = write_section(L["both_exist"], both_exist, row)
        row = write_section(L["only_purchase"], only_purchase, row)
        row = write_section(L["only_actual"], only_actual, row)

        # 备注
        if self.note:
            sheet.write(row + 1, 0, self.note, workbook.add_format({"italic": True}))

        workbook.close()
        output.seek(0)
        data = output.read()

        filename = f"{L['title']}_{order.name}.xlsx"
        self.write({
            "file_name": filename,
            "file_data": base64.b64encode(data),
        })

        return {
            "type": "ir.actions.act_url",
            "url": (
                f"/web/content/?model={self._name}&id={self.id}"
                f"&field=file_data&filename_field=file_name&download=true"
            ),
            "target": "self",
        } 